#!/bin/bash

# 定义数据库路径和输出文件路径
base_path="/opt/shells/dbip"
DB_PATH="$base_path/dbip.db"
OUTPUT_FILE="$base_path/ipset_batch.txt"
yyyymm=$(date +"%Y-%m")
d_sname="dbip-city-lite-$yyyymm.csv.gz"
download_name="$base_path/csv/$d_sname"
mkdir -p "$base_path/csv"

 wget  "https://download.db-ip.com/free/$d_sname" -O "$download_name"

if [[ ! -f "$download_name" ]]; then
 echo "找不到文件:$download_name"
 exit 1
fi
if [[ ! -s "$download_name" ]]; then
 echo "文件未下载完成:$download_name"
 exit 1
fi
 gzip -d "$download_name"

para=$(find "$base_path" -name "*$yyyymm.csv"|head -n 1)
# 检查是否找到了文件
if [ -z "$para" ]; then
    echo "未找到文$yyyymm.csv文件."
     exit 1
fi
python3 dbip.py $para
echo "csv文件解析完成"

# 检查数据库文件是否存在
if [ ! -f "$DB_PATH" ]; then
    echo "数据库文件 $DB_PATH 不存在"
    exit 1
fi
 > "$OUTPUT_FILE"
# 使用 SQLite 命令行工具导出数据
sqlite3 "$DB_PATH" <<EOF
.mode list
.separator "\n"
.header off
.output "$OUTPUT_FILE"
SELECT 
    'add dbip_' || addr_type || '_' || 
    CASE 
        WHEN instr(stateprov, ' ') > 0 THEN substr(stateprov, 1, instr(stateprov, ' ') - 1)
        ELSE stateprov 
    END || ' ' || ip_start || '-' || ip_end || ' comment "' || stateprov || '" -exist'
FROM 
    dbip_lookup where addr_type !='IPv6';
EOF
readarray -t cities < <(sqlite3 "$DB_PATH" "select 'dbip_' || addr_type || '_' ||
    CASE
        WHEN instr(stateprov, ' ') > 0 THEN substr(stateprov, 1, instr(stateprov, ' ') - 1)
        ELSE stateprov
    END newcol 
     from dbip_lookup group by CASE 
        WHEN instr(stateprov, ' ') > 0 THEN substr(stateprov, 1, instr(stateprov, ' ') - 1)
        ELSE stateprov
    END ;")
exist_ipset=($(ipset -n list))

for city in "${cities[@]}"; do
 if ! [[ " ${exist_ipset[@]}" =~ "$city" ]]; then
   ipset create "$city" hash:net comment 
echo $city
 fi
echo $city
done
  ipset restore < "$OUTPUT_FILE" 
echo "ipset 设置完成"